SELECT i.Shelf,
i.LocationID,
SUM(i.Quantity) AS Total
FROM Production.ProductInventory i
GROUP BY CUBE(i.Shelf, i.LocationID);


--including grand Total

SELECT i.Shelf,
p.Name,
SUM(i.Quantity) AS Total
FROM Production.ProductInventory i
INNER JOIN Production.Product p
ON i.ProductID = p.ProductID
GROUP BY ROLLUP(i.Shelf, p.Name);



SELECT i.Shelf,
p.Name,
SUM(i.Quantity) AS Total,
case
when Grouping(i.shelf)=1 then 'Shelf total' else 'not total' end  as "Shelf groupgin",
case
when Grouping(p.name)=1 then 'name totoal' else 'not name total' end  as "name grouping"
FROM Production.ProductInventory i
INNER JOIN Production.Product p
ON i.ProductID = p.ProductID
GROUP BY i.Shelf, p.Name with CUBE  





SELECT i.Shelf,
i.LocationID,
p.Name,
SUM(i.Quantity) AS Total
FROM Production.ProductInventory i
INNER JOIN Production.Product p
ON i.ProductID = p.ProductID
WHERE Shelf IN ('A', 'C')
AND Name IN ('Chain', 'Decal', 'Head Tube')
GROUP BY GROUPING SETS((i.Shelf),
(i.Shelf, p.Name),
(i.LocationID, p.Name));


SELECT i.Shelf,
i.LocationID,
CASE WHEN GROUPING(i.Shelf) = 0 AND GROUPING(i.LocationID) = 1 THEN 'Shelf Total'
WHEN GROUPING(i.Shelf) = 1 AND GROUPING(i.LocationID) = 0 THEN 'Location Total'
WHEN GROUPING(i.Shelf) = 1 AND GROUPING(i.LocationID) = 1 THEN 'Grand Total'
ELSE 'Regular Row'
END AS RowType,
SUM(i.Quantity) AS Total
FROM Production.ProductInventory i
WHERE LocationID = 2
GROUP BY CUBE(i.Shelf, i.LocationID);


USE AdventureWorks2012;
GO
SELECT SalesQuota, SUM(SalesYTD) 'TotalSalesYTD', GROUPING(SalesQuota) AS 'Grouping'
FROM Sales.SalesPerson
GROUP BY SalesQuota WITH ROLLUP
